package net.huashitong.user.dao;

import java.util.List;

import com.sirdc.modules.utils.StringUtils;
import net.huashitong.Indicators.dataBean.IndexDataDTO;
import net.huashitong.user.entity.Collection;
import net.huashitong.user.filter.CollectionFilter;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.sirdc.modules.core.dao.GenericQuery;
import com.sirdc.modules.core.dao.StringDao;

@Repository
public class CollectionDao extends StringDao<Collection> {

	public List<Collection> queryByUserId(CollectionFilter filter) {
		GenericQuery query = createQuery("obj");
		query.append(" and obj.userId = :userId order By obj.sysId desc").setParam("userId", filter.getUserId());
		return query.listResult();
	}

	/*public Collection getByuserIdAndAlertId(EarlyWarnFilter filter) {
		GenericQuery query = createQuery("obj");
		query.append(" and obj.userId = :userId").setParam("userId", filter.getUserId());
		query.append(" and obj.alertId = :alertId").setParam("alertId", filter.getSysId());
		return query.singleResult();
	}
*/
	public Collection getByUserIdAndAlertId(CollectionFilter filter) {
		GenericQuery query = createQuery("obj");
		query.append(" and obj.userId = :userId").setParam("userId", filter.getUserId());
		query.append(" and obj.alertId = :alertId").setParam("alertId", filter.getAlertId());
		return query.singleResult();
	}

	public List<Collection> query(CollectionFilter filter) {
		GenericQuery query = createQuery("obj");
		if (StringUtils.isNotBlank(filter.getUserId())){
			query.append(" and obj.userId =:userId").setParam("userId",filter.getUserId());
		}
		if (StringUtils.isNotBlank(filter.getCodeId())){
			query.append(" and obj.codeId =:codeId").setParam("codeId",filter.getCodeId());
		}
		if (StringUtils.isNotBlank(filter.getCodeType())){
			query.append(" and obj.codeType =:codeType").setParam("codeType",filter.getCodeType());
		}
		if (StringUtils.isNotBlank(filter.getAreaCode())){
			query.append(" and obj.areaCode =:areaCode").setParam("areaCode",filter.getAreaCode());
		}
		return query.listResult();
	}

	public List querySearchCollection(CollectionFilter filter)
	{
		StringBuffer sql  = new StringBuffer();
		sql.append(" select a.sys_id      \"sysId\" , ");
		sql.append(" 	a.user_id  \"userId\" , ");
		sql.append(" 	a.area_code 		 \"areaCode\", ");
		sql.append(" 	b.area_name		 \"areaName\" ,");
		sql.append(" 	a.code_id   		 \"codeId\",	");
		sql.append(" 	c.code_name       \"codeName\" ,  ");
		sql.append(" 	d.cumulative_ratio	 \"cumulativeRatio\" ,");
		sql.append(" 	d.current_value	 \"currentValue\" ,");
		sql.append(" 	d.cumulative_value \"cumulativeValue\" ,");
		sql.append("    d.area_type  \"areaType\" ,");
        sql.append("    d.code_Type \"codeType\" ,");
		sql.append("    c.unit  \"unit\", ");
		sql.append(" 	d.datetime 	 \"datetime\"  ");
		sql.append(" from t_gis_collection a, ba01 b, ab01 c, ac04 d ");
		sql.append(" where a.area_code = b.area_code ");
		sql.append(" and c.code_id = a.code_id ");
		sql.append(" and a.code_type = c.code_type ");
		sql.append(" and a.area_type = c.area_type ");
		sql.append(" and d.area_code = a.area_code ");
		sql.append(" and a.code_id = d.code_id ");
		sql.append(" and a.code_type = d.code_type ");
		sql.append(" and a.user_id = '"+filter.getUserId()+"' ");
		if(StringUtils.isNotBlank(filter.getDatetime()))
		{
			sql.append(" and c.datetime like '%"+filter.getDatetime()+"%' ");
		}
		if(StringUtils.isNotBlank(filter.getCodeName()))
		{
			sql.append(" and c.code_name like '%"+filter.getCodeName()+"%' ");
		}
		if(StringUtils.isNotBlank(filter.getAreaName()))
		{
			sql.append(" and b.area_name like '%"+filter.getAreaName()+"%' ");
		}

		SQLQuery query = getSession().createSQLQuery(sql.toString());
		query.setResultTransformer(Transformers.aliasToBean(IndexDataDTO.class));
		List<IndexDataDTO> list  = (List<IndexDataDTO>)query.list();

		return list;
	}


}
